package com.jsmtr.rms.service.impl;

import com.jsmtr.rms.dao.DaoService;
import com.jsmtr.rms.entity.DNZResume;
import com.jsmtr.rms.entity.DUserResume;
import com.jsmtr.rms.entity.HRZZUser;
import com.jsmtr.rms.service.IExcelExportService;
import com.jsmtr.rms.utils.BeanTools;
import com.jsmtr.rms.utils.StringTools;
import com.jsmtr.rms.utils.db.DbUtil;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * @author: cyx
 * @description:
 * @date: Created in 2018/7/10 11:39
 * @modified by:
 */
@Service
public class ExcelExportService implements IExcelExportService {

    @Resource
    DaoService serviceDao;

    public void setServiceDao(DaoService serviceDao) {
        this.serviceDao = serviceDao;
    }

    @Override
    public List<Object[]> queryUserResumeByProjectId(String pid, String selectColumns) throws Exception {
        //String sql="select "+selectColumns+" from DUserResume where proid='"+pid+"' ";
        String sql=
                "select distinct "+selectColumns+"\n" +
                        //"                  from Duserresume t,\n" +
                        "                  from VIEW_DUSERRESUME t\n" +
                      /*  "                          ,(SELECT rid,schoolname 专科院校, majorname 专科专业\n" +
                        "                          FROM   Dusereducation where  educationcode='15' order by educationcode desc,endtime asc)  zk,\n" +
                        "                          (SELECT rid,schoolname 本科院校, majorname 本科专业\n" +
                        "                          FROM Dusereducation where  educationcode='14' order by educationcode desc,endtime asc)  bk,\n" +
                        "                          (SELECT rid,schoolname 研究生院校,majorname 研究生专业\n" +
                        "                          FROM  Dusereducation where  educationcode='11' order by educationcode desc,endtime asc) yjs\n" +*/
                        "                 where 1=1\n" +
                        "                   and proid='"+pid+"'\n"+
                        /*"                   and t.id=pg.rid(+)\n" +
                        "                   and t.id=zk.rid(+)\n" +
                        "                   and t.id=bk.rid(+)\n" +
                        "                   and t.id=yjs.rid(+)";*/
                        "";
        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql);
        return dUserResumeList;
    }

    @Override
    public List<Object[]> queryUserResumeBySearch(String pid, String selectColumns, Map param, DUserResume userResume) throws Exception {
        //String sql="select "+selectColumns+" from DUserResume where proid='"+pid+"' ";
        StringBuffer sql= new StringBuffer(
                " select "+selectColumns+"  from ( \n"+
                "  select distinct t.*" +
                        "                  from VIEW_DUSERRESUME t\n" +
                        "                 where 1=1\n" +
                        "                   and proid='"+pid+"'\n"+

                        "");
        Object ids= param.get("ids");
        if(StringTools.trimNotEmpty(ids)) {
            String[] idArray=ids.toString().split(",");
            String userids="";
            if (idArray!=null&&idArray.length>0){
                for (String userid : idArray) {
                    userids+="'"+userid+"',";
                }
                userids=userids.substring(0,userids.length()-1);
                sql.append("		and  id in ("+userids+") \n");
            }


        }
        fillSearchCon(sql,param);
        sql.append(BeanTools.hqlWhere(userResume, "t"));
        sql.append(" order by t.name asc ");
       sql.append(")");
        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }


    @Override
    public List<Object[]> queryDNZResumeBySearch(String pid, String selectColumns, Map param, DNZResume dnzResume) throws Exception {
        //String sql="select "+selectColumns+" from DUserResume where proid='"+pid+"' ";
        StringBuffer sql= new StringBuffer(
                " select "+selectColumns+"  from ( \n"+
                        "  select distinct t.*" +
                        "                  from VIEW_DNZRESUME t\n" +
                        "                 where 1=1\n" +
                        "                   and proid='"+pid+"'\n"+

                        "");
        Object ids= param.get("ids");
        if(StringTools.trimNotEmpty(ids)) {
            String[] idArray=ids.toString().split(",");
            String userids="";
            if (idArray!=null&&idArray.length>0){
                for (String userid : idArray) {
                    userids+="'"+userid+"',";
                }
                userids=userids.substring(0,userids.length()-1);
                sql.append("		and  id in ("+userids+") \n");
            }


        }
        fillSearchCon(sql,param);
        sql.append(BeanTools.hqlWhere(dnzResume, "t"));
        sql.append(" order by t.name asc ");
        sql.append(")");
        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }

    @Override
    public List<Object[]> queryZZUserBySearch(HRZZUser hrzzUser, Map param, String selectColumns) throws Exception {
        StringBuffer sql=new StringBuffer("select "+selectColumns+" from HR_ZZUSER p ,  DUserResume u ,DOMS_User m ,Doms_Confirm c where 1=1 and p.code=u.cardno(+) and p.pid=u.proid(+) and p.id=m.userid(+) and m.id=c.msuserid(+) ");
        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");
        Object graduationyear = param.get("graduationyear");
        Object msarrange = param.get("msarrange");
        Object tjarrange = param.get("tjarrange");
        Object msresult = param.get("msresult");
        Calendar cal = Calendar.getInstance();
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            // String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            // SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            sql.append(" and substr(p.code,7,4)<='"+cal.get(Calendar.YEAR)+"' " );
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            // String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql.append(" and substr(p.code,7,4)>='"+cal.get(Calendar.YEAR)+"' ");
        }

        if (StringTools.notEmpty(heightfrom)) {
            sql.append(" and u.height>='"+heightfrom+"' ");
        }
        if (StringTools.notEmpty(heightto)) {
            sql.append(" and u.height<='"+heightto.toString()+"' ");
        }
        if (StringTools.notEmpty(graduationyear)) {
            sql.append(" and p.graduationtime like '"+graduationyear+"%'");
        }


        sql.append(BeanTools.hqlWhere(hrzzUser, "p"));

        if (StringTools.trimNotEmpty(msarrange)) {
            sql.append(" and m.address " + msarrange + " ");
        }

        if (StringTools.trimNotEmpty(tjarrange)) {
            sql.append(" and p.tjaddress " + tjarrange + " ");
        }
        if (StringTools.trimNotEmpty(msresult)) {
            sql.append(" and m.result='" + msresult + "' ");
        }
        //2019-11-6 新增成绩区间条件筛选
        Object scorearea =param.get("scorearea");
        if (StringTools.notEmpty(param.get("scorearea"))) {
            if (scorearea.toString().equalsIgnoreCase("L60")){
                sql.append(" and to_number(nvl(m.point,0))  <60 ");
            }else if(scorearea.toString().equalsIgnoreCase("R60")){
                sql.append(" and to_number(nvl(m.point,0))  >=60 ");
            }

        }
        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }

    @Override
    public List<Object[]> queryMSUserResumeBySearch(String pid, String selectColumns, Map param, DUserResume userResume) throws Exception {

        StringBuffer sql= new StringBuffer(
                " select "+selectColumns+"  from ( \n"+
                        "  select distinct t.*," +
                        //"pg.普高专业,zk.专科院校,zk.专科专业,bk.本科院校,bk.本科专业,yjs.研究生院校,yjs.研究生专业  \n" +
                        "                  from VIEW_DUSERRESUME t,DOMS_User m,DOMS_Confirm c \n" +
                       /* "                         ,(SELECT rid,schoolname 普高院校, majorname 普高专业\n" +
                        "                          FROM   Dusereducation where  educationcode='20' order by educationcode desc,endtime asc) pg,\n" +
                        "                          (SELECT rid,schoolname 专科院校, majorname 专科专业\n" +
                        "                          FROM   Dusereducation where  educationcode='15' order by educationcode desc,endtime asc)  zk,\n" +
                        "                          (SELECT rid,schoolname 本科院校, majorname 本科专业\n" +
                        "                          FROM Dusereducation where  educationcode='14' order by educationcode desc,endtime asc)  bk,\n" +
                        "                          (SELECT rid,schoolname 研究生院校,majorname 研究生专业\n" +
                        "                          FROM  Dusereducation where  educationcode='11' order by educationcode desc,endtime asc) yjs\n" +*/
                        "                 where 1=1\n" +
                        "            and t.id=m.userid(+) and m.id=c.msuserid(+) \n"+
                        "              and proid='"+pid+"'\n"+
                        "              ");
                     /*   "                   and t.id=pg.rid(+)\n" +
                        "                   and t.id=zk.rid(+)\n" +
                        "                   and t.id=bk.rid(+)\n" +
                        "                   and t.id=yjs.rid(+)");*/
        fillSearchCon(sql,param);
        sql.append(BeanTools.hqlWhere(userResume, "t"));
        sql.append(" order by t.name asc ");
        sql.append(")");

        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }

    @Override
    public List<Object[]> queryBSUserResumeBySearch(String pid, String selectColumns, Map param, DUserResume userResume) throws Exception {
        StringBuffer sql= new StringBuffer(
                " select "+selectColumns+"  from ( \n"+
                        "  select distinct t.*" +
                        //",pg.普高院校,pg.普高专业,zk.专科院校,zk.专科专业,bk.本科院校,bk.本科专业,yjs.研究生院校,yjs.研究生专业  \n" +
                        "                  from VIEW_DUSERRESUME t,DOBS_User b \n" +
                      /*  "                         ,(SELECT rid,schoolname 普高院校, majorname 普高专业\n" +
                        "                          FROM   Dusereducation where  educationcode='20' order by educationcode desc,endtime asc) pg,\n" +
                        "                          (SELECT rid,schoolname 专科院校, majorname 专科专业\n" +
                        "                          FROM   Dusereducation where  educationcode='15' order by educationcode desc,endtime asc)  zk,\n" +
                        "                          (SELECT rid,schoolname 本科院校, majorname 本科专业\n" +
                        "                          FROM Dusereducation where  educationcode='14' order by educationcode desc,endtime asc)  bk,\n" +
                        "                          (SELECT rid,schoolname 研究生院校,majorname 研究生专业\n" +
                        "                          FROM  Dusereducation where  educationcode='11' order by educationcode desc,endtime asc) yjs\n" +*/
                        "                 where 1=1\n" +
                        "            and t.id=b.userid(+) and t.fid=b.fid(+) \n"+
                        "                   and proid='"+pid+"'\n"+
                       /* "                   and t.id=pg.rid(+)\n" +
                        "                   and t.id=zk.rid(+)\n" +
                        "                   and t.id=bk.rid(+)\n" +
                        "                   and t.id=yjs.rid(+)");*/
                        "");
        fillSearchCon(sql,param);
        sql.append(BeanTools.hqlWhere(userResume, "t"));
        sql.append(" order by t.name asc ");
        sql.append(")");
        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }


    @Override
    public List<Object[]> queryUserResumeByTemplate(String pid, String ids) throws Exception {
        //String sql="select "+selectColumns+" from DUserResume where proid='"+pid+"' ";
        StringBuffer sql= new StringBuffer(
                "SELECT ROWNUM\n" +
"	序号,\n" +
"	jobname 应聘岗位,\n" +
"	NAME 姓名,\n" +
"	SEXNAME 性别, \n" +
"	AGE 年龄,\n" +
"	JG 籍贯,\n" +
"	CARDNO 身份证号,\n" +
"	ZZMMNAME 政治面貌,\n" +
"	trunc(months_between(SYSDATE, WORKDATE) / 12) 工龄,\n" +
"	university 毕业院校,\n" +
"	majorname 专业,\n" +
"	educationname 最高学历,\n" +
"	graduationtime 最高学历取得时间,\n" +
"	DEGREENAME 最高学位,\n" +
"	DENDTIME 最高学位取得时间,\n" +
"	WKENDTIME 最近一家工作时间,\n" +
"	wkcompanyname 最近一家工作单位,\n" +
"	wkposition 最近一份工作岗位,\n" +
"	UPOSTSPECIALTY 获得职称,\n" +
"	expectedsalaryamount 期望薪酬,\n" +
"	TEL 联系电话\n" +
" FROM\n" +
"	(SELECT\n" +
"		dur.id,\n" +
"		dur.adddate,\n" +
"		dur.attendancetime,\n" +
"		dur.bdaddress,\n" +
"		dur.bddate,\n" +
"		dur.bdlxdh,\n" +
"		dur.bdlxr,\n" +
"		dur.bdmsg,\n" +
"		dur.bdremark,\n" +
"		( CASE dur.bdstate WHEN '未报到' THEN '' ELSE dur.bdstate END ) AS bdstate,\n" +
"		dur.bdtime,\n" +
"		dur.bduid,\n" +
"		dur.bduname,\n" +
"		TRUNC( months_between( SYSDATE, dur.birthday ) / 12 ) AS birthday,\n" +
"		dur.cardaddress,\n" +
"		concat( '', dur.cardno ) cardno,\n" +
"		dur.code,\n" +
"		dur.distribution,\n" +
"		dur.editdate,\n" +
"		dur.editusername,\n" +
"		dur.edituserid,\n" +
"		dur.email,\n" +
"		dur.expectedsalaryamount,\n" +
"		dur.fid,\n" +
"		dur.fqdate,\n" +
"		dur.fqfid,\n" +
"		dur.fqstate,\n" +
"		wk.companyname wkcompanyname,\n" +
"		wk.department wkdepartment,\n" +
"		wk.position wkposition,\n" +
"		wk.endtime wkendtime,\n" +
"		mxdusereducation.schoolname university,\n" +
"		mxdusereducation.majorname majorname,\n" +
"		mxdusereducation.endtime graduationtime,\n" +
"		mxdusereducation.educationcode education,\n" +
"		mxdusereducation.educationname educationname,\n" +
"		MXDEGREECODE.ENDTIME DENDTIME,\n" +
"		MXDEGREECODE.DEGREENAME DEGREENAME,\n" +
"		dur.head,\n" +
"		dur.height,\n" +
"		dur.hkcode,\n" +
"		dur.hkname,\n" +
"		dur.hometel,\n" +
"		dur.hukouaddress,\n" +
"		dur.jg,\n" +
"		dur.jid,\n" +
"		jobs.jobdepart,\n" +
"		dur.jname jobname,\n" +
"		dur.mcode,\n" +
"		dur.mname,\n" +
"		dur.mzcode,\n" +
"		dur.mzname,\n" +
"		dur.name,\n" +
"		dur.nowaddress,\n" +
"		dur.othcode,\n" +
"		dur.othdate,\n" +
"		dur.othlevel,\n" +
"		dur.othname,\n" +
"		dur.othorg,\n" +
"		dur.othtypecode,\n" +
"		dur.othtypename,\n" +
"		dur.othzy,\n" +
"		dur.path,\n" +
"		dur.pid,\n" +
"		dur.pname,\n" +
"		dur.postcompany,\n" +
"		dur.postgettime,\n" +
"		dur.postlevelcode,\n" +
"		dur.postlevelname,\n" +
"		dur.postspecialty,\n" +
"		dur.posttypecode,\n" +
"		dur.posttypename,\n" +
"		dur.presentsalaryamount,\n" +
"		dur.proid,\n" +
"		dur.proname,\n" +
"		dur.qlgettime,\n" +
"		dur.qllevelcode,\n" +
"		dur.qllevelname,\n" +
"		dur.qllevelpid,\n" +
"		dur.qlorg,\n" +
"		dur.qltype,\n" +
"		dur.sexcode,\n" +
"		dur.sexname,\n" +
"		dur.state,\n" +
"		dur.sxbz,\n" +
"		dur.tel,\n" +
"		dur.tjaddress,\n" +
"		dur.tjdate,\n" +
"		dur.tjlxdh,\n" +
"		dur.tjlxr,\n" +
"		dur.tjmsg,\n" +
"		dur.tjremark,\n" +
"		( CASE dur.tjstate WHEN '未体检' THEN '' ELSE dur.tjstate END ) AS tjstate,\n" +
"		dur.tjtime,\n" +
"		dur.tjuid,\n" +
"		dur.tjuname,\n" +
"		dur.userid,\n" +
"		dur.workexperience,\n" +
"		dur.zldate,\n" +
"		dur.zldelay,\n" +
"		dur.zljstime,\n" +
"		dur.zlkstime,\n" +
"		dur.zlmsg,\n" +
"		dur.zlmsgcont,\n" +
"		dur.zlremark,\n" +
"		dur.zlstate,\n" +
"		dur.zluid,\n" +
"		dur.zluname,\n" +
"		dur.zsdate,\n" +
"		dur.zsremark,\n" +
"		( CASE dur.zsstate WHEN '未政审' THEN '' ELSE dur.zsstate END ) AS zsstate,\n" +
"		dur.zsuid,\n" +
"		dur.zsuname,\n" +
"		dur.zzmmcode,\n" +
"		dur.zzmmname,\n" +
"		dur.op1,\n" +
"		dur.op2,\n" +
"		dur.op3,\n" +
"		dur.op4,\n" +
"		dur.op5,\n" +
"		dur.rcode,\n" +
"		dur.region,\n" +
"		dur.gwdate,\n" +
"		dur.gwuid,\n" +
"		dur.gwuname,\n" +
"		dur.gwstate,\n" +
"		dur.op6,\n" +
"		dur.rfid,\n" +
"		dur.orid,\n" +
"		dur.nrid,\n" +
"		dur.njname,\n" +
"		dur.changedate,\n" +
"		dur.qllevelpname,\n" +
"		dur.qllevelscode,\n" +
"		dur.qllevelsname,\n" +
"		dur.rcount,\n" +
"		dur.op7,\n" +
"		dur.sxstate,\n" +
"		dur.sxremark,\n" +
"		dur.sxuid,\n" +
"		dur.sxuname,\n" +
"		dur.sxdate,\n" +
"		dur.op9,\n" +
"		dur.qystate,\n" +
"		dur.qyremark,\n" +
"		dur.qyuid,\n" +
"		dur.qyuname,\n" +
"		dur.qydate,\n" +
"		dur.recommenddate,\n" +
"		ur.AGE,\n" +
"		ur.WORKDATE，\n" +
"		posr.UPOSTSPECIALTY--,\n" +
"		--ur.WORKEXPERIENCE\n" +
"	FROM\n" +
"		duserresume dur,\n" +
"		view_maxeducation mxdusereducation,\n" +
"		view_maxdegree MXDEGREECODE,\n" +
"		VIEW_MAXWORK wk,\n" +
"		project_info_jobs jobs,\n" +
"		USERRESUME  ur ,\n" +
"		(select DISTINCT ur.CARDNO,wm_concat(m.POSTSPECIALTY) UPOSTSPECIALTY from USERMAJOR m,USERRESUME ur where m.RID=ur.ID GROUP BY ur.CARDNO) posr\n" +
"	WHERE\n" +
"		1 = 1 \n" +
"		AND dur.id = mxdusereducation.rid ( + ) \n" +
"		AND dur.id = MXDEGREECODE.rid ( + )\n" +
"		AND dur.jid = jobs.id ( + ) \n" +
"		AND dur.id = wk.rid ( + ) \n" +
"		and dur.CARDNO=ur.CARDNO\n" +
"		and dur.CARDNO=posr.CARDNO（+）\n" +
"		AND dur.FQSTATE = '未放弃' \n" +
                        "		AND dur.proid = '"+pid+"'");

                        if(StringTools.trimNotEmpty(ids)) {
                           sql.append("		AND dur.id in (" + ids + ") \n");
                        }

            sql.append("	ORDER BY dur.JNAME ASC )");

        List<Object[]> dUserResumeList= serviceDao.executeSQLQuery(sql.toString());
        return dUserResumeList;
    }


    /**
     * 填充区间段查询条件,注意表别名为t
     * @param sql Sql语句类型为StringBuffer
     * @param param
     * @return
     */
    public StringBuffer fillSearchCon(StringBuffer sql,Map param) throws ParseException {
        Object agefrom = param.get("agefrom");
        Object ageto = param.get("ageto");
        Object heightfrom = param.get("heightfrom");
        Object heightto = param.get("heightto");
        Object graduationyear = param.get("graduationtime");
        Object jobname = param.get("jobname");
        Object msplan=param.get("msplan");
        Object msplantime=param.get("msplantime");
        Object wkcompanyname=param.get("wkcompanyname");
        Object wkdepartment =param.get("wkdepartment");
        Object wkposition= param.get("wkposition");
        Object jobdepart=param.get("jobdepart");
        Object adddate=param.get("adddate");
        Calendar cal = Calendar.getInstance();

        if (StringTools.notEmpty(jobdepart)) {
            sql.append(" and t.jobdepart like '%"+jobdepart+"%'");
        }
        if (StringTools.notEmpty(wkcompanyname)) {
            sql.append(" and t.wkcompanyname like '%"+wkcompanyname+"%'");
        }
        if (StringTools.notEmpty(wkdepartment)) {
            sql.append(" and t.wkdepartment like '%'"+wkdepartment+"'%'");
        }
        if (StringTools.notEmpty(wkposition)) {
            sql.append(" and t.wkposition like '%"+wkposition+"%'");
        }
        if (StringTools.notEmpty(adddate)) {
            sql.append(" and to_char(t.adddate,'yyyy-MM-dd') like '%"+adddate+"%'");
        }
        if (StringTools.notEmpty(agefrom)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(agefrom.toString()));
            // String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            // SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
            sql.append(" and substr(t.cardno,7,4)<='"+cal.get(Calendar.YEAR)+"' " );
        }
        if (StringTools.notEmpty(ageto)) {
            cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            // String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
            sql.append(" and substr(t.cardno,7,4)>='"+cal.get(Calendar.YEAR)+"' ");
        }

        if (StringTools.notEmpty(heightfrom)) {
            sql.append(" and t.height>='"+heightfrom+"' ");
        }
        if (StringTools.notEmpty(heightto)) {
            sql.append(" and t.height<='"+heightto.toString()+"' ");
        }
        if (StringTools.notEmpty(graduationyear)) {
            sql.append(" and t.graduationtime like '"+graduationyear+"%'");
        }

        if (StringTools.notEmpty(jobname)) {
            sql.append(" and t.jobname like '"+jobname+"%'");
        }

        if (StringTools.notEmpty(msplan)) {
            String msaddress=param.get("msplan").toString();
            if(msaddress.equalsIgnoreCase("yes")){
                sql.append(" and t.msaddress  is not null ");
            }else{
                sql.append(" and t.msaddress  is null ");
            }
            //sql.append(" and t.msaddress like '"+jobname+"%'");
        }

        if (StringTools.notEmpty(msplantime)) {
            sql.append(" and t.msstarttime like '%"+msplantime+"%'");
        }

        if (StringTools.notEmpty(param.get("msaddress"))) {
            String msaddress=param.get("msaddress").toString();
            sql.append(" and t.msaddress like '%"+msaddress+"%'");
        }

        Object ischangejob = param.get("ischangejob");
        if (StringTools.notEmpty(ischangejob)){
            if (ischangejob.toString().equalsIgnoreCase("是")){
                sql.append(" and  t.njname is not null ");
            }else  if (ischangejob.toString().equalsIgnoreCase("否")){
                sql.append(" and  t.njname is  null ");
            }

        }

        Object msresult=param.get("msresult");
        if (StringTools.notEmpty(msresult)){
                sql.append(" and  t.msresult ='"+msresult+"'");
        }

        //2019-11-6 新增成绩区间条件筛选
        Object scorearea =param.get("scorearea");
        if (StringTools.notEmpty(param.get("scorearea"))) {
            if (scorearea.toString().equalsIgnoreCase("L60")){
                sql.append(" and to_number(nvl(t.mspoint,0))  <60 ");
            }else if(scorearea.toString().equalsIgnoreCase("R60")){
                sql.append(" and to_number(nvl(t.mspoint,0))  >=60 ");
            }
        }


        Object adddatefrom=param.get("adddatefrom");
        if (StringTools.notEmpty(adddatefrom)) {
           /* cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);*/
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
            /*_map.put("adddatefrom", sdf.parse(adddatefrom.toString().trim()));
            condition.append(" and dur.adddate >= :adddatefrom");*/
            sql.append(" and t.adddate >= to_date('"+adddatefrom.toString().trim()+"','yyyy-MM-dd hh24:mi')");
        }

        Object adddateto=param.get("adddateto");
        if (StringTools.notEmpty(adddateto)) {
            /*cal.setTime(new Date());
            cal.add(Calendar.YEAR, 0 - Integer.parseInt(ageto.toString()));
            String _date = cal.get(Calendar.YEAR) + "-" + cal.get(Calendar.MONTH) + "-" + cal.get(Calendar.DATE);*/
            //SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm");
             sql.append(" and t.adddate <= to_date('"+adddateto.toString().trim()+"','yyyy-MM-dd hh24:mi')");

        }
        return sql;
    }
}
